merge_student_teacher<-function(){
  setwd(wd_data_final)
  data_teacher<-readRDS('data_teacher')
  data_student<-readRDS('data_student')
  perc.rank <- function(x) {
    y<-rank(x)/length(x)
    return(y)} 
  
   data_student<-data_student %>% group_by(town,an,unitate_de_invatamant) %>% mutate(n_students_hs_yr=n())
  

    #merge overall grades

   years<-sort(unique(data_student$an))
   #for (y in years){
   data_student<-bind_rows(lapply(years,function(y){
     teacher_temp<-data_teacher %>% 
       filter(Year<=y & (as.numeric(Year)+as.numeric(Job_Duration))>=(y-4)) %>%
       group_by(County_long,school_harmonized) %>%
       summarise(n_teacher=n(),
                  teacher_perc=sum(as.numeric(teacher_perc)*as.numeric(Hours_Mandatory),na.rm=T)/sum(as.numeric(Hours_Mandatory),na.rm=T),
                 Experience=sum(as.numeric(Experience)*as.numeric(Hours_Mandatory),na.rm=T)/sum(as.numeric(Hours_Mandatory),na.rm=T),
                 gpa_perc=sum(as.numeric(gpa_perc)*as.numeric(Hours_Mandatory),na.rm=T)/sum(as.numeric(Hours_Mandatory),na.rm=T),
                 Education_num=sum(as.numeric(Education_num)*as.numeric(Hours_Mandatory),na.rm=T)/sum(as.numeric(Hours_Mandatory),na.rm=T),
                 Teacher_Category_num=sum(as.numeric(Teacher_Category_num)*as.numeric(Hours_Mandatory),na.rm=T)/sum(as.numeric(Hours_Mandatory),na.rm=T))
     data_student_temp<-data_student %>% filter(an==y)
     data_student_temp<-  base::merge(data_student_temp,
                                                        teacher_temp,
                                                        by.x=c("judet_bac","school_harmonized"),
                                                        by.y=c("County_long","school_harmonized"),
                                                        all.x=T,
                                                        suffixes=c("",""))
     
     return(data_student_temp)
   }))

   

    #merge Romanian 
   data_student<-bind_rows(lapply(years,function(y){
    teacher_temp<-data_teacher %>% 
      filter(Year<=y & (as.numeric(Year)+as.numeric(Job_Duration))>=(y-4)) %>%
      filter(grepl('ROMAN',toupper(Subject))) %>%
      group_by(County_long,school_harmonized) %>%
      summarise(n_teacher=n(),
                teacher_perc=sum(as.numeric(teacher_perc)*as.numeric(Hours_Mandatory),na.rm=T)/sum(as.numeric(Hours_Mandatory),na.rm=T),
                Experience=sum(as.numeric(Experience)*as.numeric(Hours_Mandatory),na.rm=T)/sum(as.numeric(Hours_Mandatory),na.rm=T),
                gpa_perc=sum(as.numeric(gpa_perc)*as.numeric(Hours_Mandatory),na.rm=T)/sum(as.numeric(Hours_Mandatory),na.rm=T),
                Education_num=sum(as.numeric(Education_num)*as.numeric(Hours_Mandatory),na.rm=T)/sum(as.numeric(Hours_Mandatory),na.rm=T),
                Teacher_Category_num=sum(as.numeric(Teacher_Category_num)*as.numeric(Hours_Mandatory),na.rm=T)/sum(as.numeric(Hours_Mandatory),na.rm=T))
    
    data_student_temp<-data_student %>% filter(an==y)
    data_student_temp<-base::merge(data_student_temp,
                                      teacher_temp,
                                      by.x=c("judet_bac","school_harmonized"),
                                      by.y=c("County_long","school_harmonized"),
                                      all.x=T,
                                      suffixes=c("",".ro"))
    return(data_student_temp)
   }))

    
    #merge mandatory subject
    subjects<-data.frame(Student_Subject=c("MATE","ISTO","GEOGR","Other"),
                         Teacher_Subject=c("MATE","ISTO","GEOGR","Other"),
                         Subject=c("MATH","HISTORY","GEOGRAPHY","Other Mandatory"),stringsAsFactors=FALSE)
    
  
   
    
    data_teacher<-data_teacher %>%
      mutate(mandatory_subject_teacher='Other Mandatory') %>%
      mutate(mandatory_subject_teacher=ifelse(grepl(subjects$Teacher_Subject[1],toupper(Subject)),subjects$Subject[1],mandatory_subject_teacher)) %>%
      mutate(mandatory_subject_teacher=ifelse(grepl(subjects$Teacher_Subject[2],toupper(Subject)),subjects$Subject[2],mandatory_subject_teacher)) %>%
      mutate(mandatory_subject_teacher=ifelse(grepl(subjects$Teacher_Subject[3],toupper(Subject)),subjects$Subject[3],mandatory_subject_teacher))          
    
    data_student<-data_student %>%
      mutate(mandatory_subject_student='Other Mandatory') %>%
      mutate(mandatory_subject_student=ifelse(grepl(subjects$Student_Subject[1],toupper(disciplina_obligatorie_scris)),subjects$Subject[1],mandatory_subject_student)) %>%
      mutate(mandatory_subject_student=ifelse(grepl(subjects$Student_Subject[2],toupper(disciplina_obligatorie_scris)),subjects$Subject[2],mandatory_subject_student)) %>%
      mutate(mandatory_subject_student=ifelse(grepl(subjects$Student_Subject[3],toupper(disciplina_obligatorie_scris)),subjects$Subject[3],mandatory_subject_student)) 
    
    data_student<-bind_rows(lapply(years,function(y){
  
      teacher_temp<-data_teacher %>% 
      filter(Year<=y & (as.numeric(Year)+as.numeric(Job_Duration))>=(y-4)) %>%
      filter(mandatory_subject_teacher %in% subjects$Subject) %>%
      group_by(County_long,school_harmonized,mandatory_subject_teacher) %>%
        summarise(n_teacher=n(),
                  teacher_perc=sum(as.numeric(teacher_perc)*as.numeric(Hours_Mandatory),na.rm=T)/sum(as.numeric(Hours_Mandatory),na.rm=T),
                  Experience=sum(as.numeric(Experience)*as.numeric(Hours_Mandatory),na.rm=T)/sum(as.numeric(Hours_Mandatory),na.rm=T),
                  gpa_perc=sum(as.numeric(gpa_perc)*as.numeric(Hours_Mandatory),na.rm=T)/sum(as.numeric(Hours_Mandatory),na.rm=T),
                  Education_num=sum(as.numeric(Education_num)*as.numeric(Hours_Mandatory),na.rm=T)/sum(as.numeric(Hours_Mandatory),na.rm=T),
                  Teacher_Category_num=sum(as.numeric(Teacher_Category_num)*as.numeric(Hours_Mandatory),na.rm=T)/sum(as.numeric(Hours_Mandatory),na.rm=T))
    
    data_student_temp<-data_student %>% filter(an==y)
    data_student_temp<-base::merge(data_student_temp,teacher_temp,by.x=c("judet_bac","school_harmonized","mandatory_subject_student"),by.y=c("County_long","school_harmonized","mandatory_subject_teacher"),all.x=T,suffixes=c("",".mand"))
    
    return(data_student_temp)
    }))

    
    
    
    #merge elective subjects
    subjects<-data.frame(Student_Subject=c("GEOGR","ANATOM","FIZIC","BIOLOG","LOGIC","MARKET","CHIMIE","INFORM","ECON","PSIHO","FILOSOF","Other"),
                         Teacher_Subject=c("GEOGR","BIOLOG","FIZIC","BIOLOG","FILO","ECON","CHIMIE","INFORM","ECON","PSIHO","FILOSOF","Other"),
                         Subject=c("GEOGRAPHY","BIOLOGY","PHYSICS","BIOLOGY","PHILOSOPHY","ECONOMICS","CHEMISTRY","CS","ECONOMICS","PSYCHOLOGY","PHILOSOPHY","Other Elective"),
                         stringsAsFactors=FALSE)
      
    data_teacher<-data_teacher %>%
                mutate(elective_subject_teacher='Other Elective') %>%
                mutate(elective_subject_teacher=ifelse(grepl(subjects$Teacher_Subject[1],toupper(Subject)),subjects$Subject[1],elective_subject_teacher)) %>%
                mutate(elective_subject_teacher=ifelse(grepl(subjects$Teacher_Subject[2],toupper(Subject)),subjects$Subject[2],elective_subject_teacher)) %>%
                mutate(elective_subject_teacher=ifelse(grepl(subjects$Teacher_Subject[3],toupper(Subject)),subjects$Subject[3],elective_subject_teacher)) %>%
                mutate(elective_subject_teacher=ifelse(grepl(subjects$Teacher_Subject[4],toupper(Subject)),subjects$Subject[4],elective_subject_teacher)) %>%
                mutate(elective_subject_teacher=ifelse(grepl(subjects$Teacher_Subject[5],toupper(Subject)),subjects$Subject[5],elective_subject_teacher)) %>%
                mutate(elective_subject_teacher=ifelse(grepl(subjects$Teacher_Subject[6],toupper(Subject)),subjects$Subject[6],elective_subject_teacher)) %>%
                mutate(elective_subject_teacher=ifelse(grepl(subjects$Teacher_Subject[7],toupper(Subject)),subjects$Subject[7],elective_subject_teacher)) %>%
                mutate(elective_subject_teacher=ifelse(grepl(subjects$Teacher_Subject[8],toupper(Subject)),subjects$Subject[8],elective_subject_teacher))  %>%
                mutate(elective_subject_teacher=ifelse(grepl(subjects$Teacher_Subject[9],toupper(Subject)),subjects$Subject[9],elective_subject_teacher)) %>% 
                mutate(elective_subject_teacher=ifelse(grepl(subjects$Teacher_Subject[10],toupper(Subject)),subjects$Subject[10],elective_subject_teacher)) %>% 
                mutate(elective_subject_teacher=ifelse(grepl(subjects$Teacher_Subject[11],toupper(Subject)),subjects$Subject[11],elective_subject_teacher))         
     
    data_student<-data_student %>%
      mutate(elective_subject_student='Other Elective') %>%
      mutate(elective_subject_student=ifelse(grepl(subjects$Student_Subject[1],toupper(disciplina_alegere_aria_culiculara)),subjects$Subject[1],elective_subject_student)) %>%
      mutate(elective_subject_student=ifelse(grepl(subjects$Student_Subject[2],toupper(disciplina_alegere_aria_culiculara)),subjects$Subject[2],elective_subject_student)) %>%
      mutate(elective_subject_student=ifelse(grepl(subjects$Student_Subject[3],toupper(disciplina_alegere_aria_culiculara)),subjects$Subject[3],elective_subject_student)) %>%
      mutate(elective_subject_student=ifelse(grepl(subjects$Student_Subject[4],toupper(disciplina_alegere_aria_culiculara)),subjects$Subject[4],elective_subject_student)) %>%
      mutate(elective_subject_student=ifelse(grepl(subjects$Student_Subject[5],toupper(disciplina_alegere_aria_culiculara)),subjects$Subject[5],elective_subject_student)) %>%
      mutate(elective_subject_student=ifelse(grepl(subjects$Student_Subject[6],toupper(disciplina_alegere_aria_culiculara)),subjects$Subject[6],elective_subject_student)) %>%
      mutate(elective_subject_student=ifelse(grepl(subjects$Student_Subject[7],toupper(disciplina_alegere_aria_culiculara)),subjects$Subject[7],elective_subject_student)) %>%
      mutate(elective_subject_student=ifelse(grepl(subjects$Student_Subject[8],toupper(disciplina_alegere_aria_culiculara)),subjects$Subject[8],elective_subject_student))  %>%
      mutate(elective_subject_student=ifelse(grepl(subjects$Student_Subject[9],toupper(disciplina_alegere_aria_culiculara)),subjects$Subject[9],elective_subject_student)) %>% 
      mutate(elective_subject_student=ifelse(grepl(subjects$Student_Subject[10],toupper(disciplina_alegere_aria_culiculara)),subjects$Subject[10],elective_subject_student)) %>% 
      mutate(elective_subject_student=ifelse(grepl(subjects$Student_Subject[11],toupper(disciplina_alegere_aria_culiculara)),subjects$Subject[11],elective_subject_student)) 
    
     

    data_student<-bind_rows(lapply(years,function(y){
    teacher_temp<-data_teacher %>% 
      filter(elective_subject_teacher %in% subjects$Subject) %>%
      filter(Year<=y & (as.numeric(Year)+as.numeric(Job_Duration))>=(y-4)) %>%
      group_by(County_long,school_harmonized,elective_subject_teacher) %>%
      summarise(n_teacher=n(),
                teacher_perc=sum(as.numeric(teacher_perc)*as.numeric(Hours_Mandatory),na.rm=T)/sum(as.numeric(Hours_Mandatory),na.rm=T),
                Experience=sum(as.numeric(Experience)*as.numeric(Hours_Mandatory),na.rm=T)/sum(as.numeric(Hours_Mandatory),na.rm=T),
                gpa_perc=sum(as.numeric(gpa_perc)*as.numeric(Hours_Mandatory),na.rm=T)/sum(as.numeric(Hours_Mandatory),na.rm=T),
                Education_num=sum(as.numeric(Education_num)*as.numeric(Hours_Mandatory),na.rm=T)/sum(as.numeric(Hours_Mandatory),na.rm=T),
                Teacher_Category_num=sum(as.numeric(Teacher_Category_num)*as.numeric(Hours_Mandatory),na.rm=T)/sum(as.numeric(Hours_Mandatory),na.rm=T))
    
    
      
    data_student_temp<-data_student %>% filter(an==y)
    data_student_temp<-base::merge(data_student_temp,teacher_temp,by.x=c("judet_bac","school_harmonized","elective_subject_student"),by.y=c("County_long","school_harmonized","elective_subject_teacher"),all.x=T,suffixes=c("",".elect"))
    return(data_student_temp)
    }))
    
  setwd(wd_data_final)
  saveRDS(data_student,"data_student_teacher")
  return(data_student)
}